//
//  DatabaseManager.m
//  iEasyBill
//
//  Created by student on 14-5-5.
//  Copyright (c) 2014年 com.yly16. All rights reserved.
//

#import "DatabaseManager.h"
#import "FMDatabaseAdditions.h"
#define kBillSqlitedbKey @"SqlBillNote.db"
#define kBillList @"iBillList"
#define kBillNoteList @"iBillNoteList"
#define kBillAccount @"iBillAccount"
#define kBillType @"iBillType"
#define kBillAccountSub @"iBillAccountSub"
#define kBillTypeSub @"iBillTypeSub"

@implementation DatabaseManager
@synthesize datalist;

static DatabaseManager *_manager = nil;//第一步：静态实例，并初始化。

+(DatabaseManager *)shareManager
{
    @synchronized (self)
    {
        if (_manager == nil)
        {
            _manager = [[DatabaseManager alloc] init];
        }
    }
    return _manager;
}

-(instancetype)init
{
    self=[super init];
    if (self) {
        self.datalist=[[NSMutableArray alloc]init];
//        //把数据库考到代码里去，以便工程移动别的mac上运行时，也有数据，不是空白cell
//        NSFileManager *DBManager = [NSFileManager defaultManager];
//        if (![DBManager fileExistsAtPath:[self iBillFileDBPath]]) {
//            NSString *currentPath = [[NSBundle mainBundle]pathForResource:@"BillNote" ofType:@"db"];
//            [DBManager copyItemAtPath:currentPath toPath:[self iBillFileDBPath] error:nil];
//        }
        self.currentNote = [[iBillNote alloc]init];
        _database = [FMDatabase databaseWithPath:[self iBillFileDBPath]];
        if ([_database open]) {
            NSLog(@"成功打开数据库, %@",[self iBillFileDBPath]);
        }

        
        if([_database executeUpdate:@"create table if not exists iBillNoteList (noteID integer primary key autoincrement,noteName text,noteImageData blob,noteBillNumber integer)"] == YES){
            NSLog(@"打开iBillNoteList表成功");
        }
        iBillNote *billNote;
        int count = [_database intForQuery:@"select count(noteID) from iBillNoteList"];
        if (count == 0) {
            billNote = [[iBillNote alloc]init];
            billNote.noteName = @"默认账本";
            [self addNewBillNote:billNote];
        }
        //iBillAccount表
        if([_database executeUpdate:@"create table if not exists iBillAccount (accountID integer primary key ,accountName text,accountMoney integer,accountImageData blob)"] == YES){
            NSLog(@"打开iBillAccount表成功");
        }
        //iBillAccountSub表
        if([_database executeUpdate:@"create table if not exists iBillAccountSub (accountSubID integer primary key autoincrement,accountSubName text,accountSubImageData blob,accountID integer)"] == YES){
            NSLog(@"打开iBillAccountSub表成功");
        }
        //iBillType表
        if([_database executeUpdate:@"create table if not exists iBillType (typeID integer primary key ,typeName text,typeImageData blob,typeIsPayOut boolean,budgetMoney integer)"] == YES){
            NSLog(@"打开iBillType表成功");
        }
        //iBillTypeSub表
        if([_database executeUpdate:@"create table if not exists iBillTypeSub (typeSubID integer primary key autoincrement,typeSubName text,typeSubImageData blob,typeID integer,budgetMoney integer)"] == YES){
            NSLog(@"打开iBillTypeSub表成功");
        }
        
        //iBillTypeSub表
        if([_database executeUpdate:@"create table if not exists userInfo (userID integer primary key,userName text,userImageData blob)"] == YES){
            NSLog(@"打开userInfo表成功");
        }
        if([_database executeUpdate:@"create table if not exists iBillList (billID integer primary key autoincrement,isPayOut integer,billMoney integer,billTime date,billImageData blob,billRemark text,billTypeID integer,billTypeSubID integer,billAccountID integer,recentBillID integer,billAccountSubID integer,billlatitude integer,billlongitude integer,billNoteID integer, foreign key (billNoteID) references iBillNoteList(noteID))"] == YES){
            NSLog(@"打开iBillList表成功");
        }
        
        //开启🔛外键功能
        [_database executeUpdate:@"PRAGMA foreign_keys = ON"];
        
    }

    return self;
}


-(NSString *)iBillFileDBPath{
    NSArray *dirList = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);//找到保存路径
    NSString *filePath=dirList[0];//将数组转化为字符串对象
    if (!filePath) {
        return nil;
    }
    NSString *path = [filePath stringByAppendingPathComponent:kBillSqlitedbKey];//将文件名添加到filePath对象所指的路径下
    return path;
    
}


-(int)numberOfBills{
    int count = [_database intForQuery:@"select count(billID) from iBillList"];
    return count;
}
-(int)numberOfBillNotes{
    NSString *sql = [NSString stringWithFormat:@"select count(noteID) from %@",kBillNoteList];
    int count = [_database intForQuery:sql];
    return count;
}
-(BOOL)addNewBill:(iBill*)theBill{            //添加一个新的账单
    //开启🔛外键功能
    [_database executeUpdate:@"PRAGMA foreign_keys = ON"];
    
    int count = [_database intForQuery:@"select count(recentBillID) from iBillList"];
    if (count < 3) {
        int billID1 = [_database intForQuery:@"select billID from iBillList where recentBillID = 1"];
        int billID2 = [_database intForQuery:@"select billID from iBillList where recentBillID = 2"];
        int billID3 = [_database intForQuery:@"select billID from iBillList where recentBillID = 3"];
        
        //>>>>2
        if (billID1 != 0 && billID2 == 0 && billID3==0) {
            [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
            
        }
        //>>>>3
        if (billID1 != 0 && billID2 != 0 && billID3==0) {
            [_database executeUpdate:@"update iBillList set recentBillID = 3 where recentBillID = 2"];
            [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
            
        }
        //>>>>4
        if (billID1 != 0 && billID2 == 0 && billID3!=0) {
            [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
        }
        //>>>>5
        if (billID1 == 0 && billID2 != 0 && billID3==0) {
            [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
        }
        count = 1;
        
        if ([_database executeUpdate:@"insert into iBillList (isPayOut,billMoney,billTime,billImageData,billRemark,billTypeID,billTypeSubID,billAccountID,billAccountSubID,billlatitude,billlongitude,billNoteID,recentBillID) values (?,?,?,?,?,?,?,?,?,?,?,?,?)",theBill.isPayOut,theBill.billMoney,[theBill.billTime dateByAddingTimeInterval:8*60*60],theBill.billImageData,theBill.billRemark,theBill.billTypeID,theBill.billTypeSubID,theBill.billAccountID,theBill.billAccountSubID,theBill.billlatitude,theBill.billlongitude,theBill.billNoteID,@(count)]
            ) {
            
            NSLog(@">>>>>>>添加账单成功");
            return YES;
        }
    }
    else{
        [_database executeUpdate:@"update iBillList set recentBillID = NULL where recentBillID = 3"];
        [_database executeUpdate:@"update iBillList set recentBillID = 3 where recentBillID = 2"];
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
        if ([_database executeUpdate:@"insert into iBillList (isPayOut,billMoney,billTime,billImageData,billRemark,billTypeID,billTypeSubID,billAccountID,billAccountSubID,billlatitude,billlongitude,billNoteID,recentBillID) values (?,?,?,?,?,?,?,?,?,?,?,?,?)",theBill.isPayOut,theBill.billMoney,[theBill.billTime dateByAddingTimeInterval:8*60*60],theBill.billImageData,theBill.billRemark,theBill.billTypeID,theBill.billTypeSubID,theBill.billAccountID,theBill.billAccountSubID,theBill.billlatitude,theBill.billlongitude,theBill.billNoteID,@(1)]
            ) {
            
            NSLog(@">>>>>>>添加账单成功");
            return YES;
        }
    }
    
    return NO;
}
-(BOOL)addNewBillNote:(iBillNote*)aNote{   //添加一个新的账本
    
    
    if ([_database executeUpdate:@"insert into iBillNoteList (noteName,noteImageData) values (?,?)",aNote.noteName,aNote.noteImageData]
           ) {
        NSLog(@"添加账本成功");
    }
    return YES;
}
-(BOOL)removeABillIndex:(iBill*)theBill{       //删
    int count = [_database intForQuery:@"select recentBillID from iBillList where billID = ?",theBill.billID];
    if (count == 3) {
        [_database executeUpdate:@"update iBillList set recentBillID = NULL where recentBillID = 3"];
        [_database executeUpdate:@"update iBillList set recentBillID = 3 where recentBillID = 2"];
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
    }
    if (count == 2) {
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 3"];
        
    }
    if (count == 1) {
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 3"];
        [_database executeUpdate:@"update iBillList set recentBillID = 1 where recentBillID = 2"];
    }
    if([_database executeUpdate:@"delete from iBillList where billID = ?",theBill.billID] == YES)
    {
        NSLog(@"删除账单成功");
        
    }
    return YES;
}

-(BOOL)removeABillNoteIndex:(iBillNote*)aNote{       //删
    [_database executeUpdate:@"delete from iBillNoteList where noteID = ?",[NSNumber numberWithInt:aNote.noteID.intValue]];
    return YES;
}
-(BOOL)ModifyBill:(iBill*)theBill{
    //int RecentCount = [_database intForQuery:@"select count(recentBillID) from iBillList"];
    //int count = [_database intForQuery:@"select recentBillID from iBillList where billID = ?",theBill.billID];
    NSInteger RecentCount = [_database intForQuery:@"select recentBillID from iBillList where billID = ?",theBill.billID];

    if (RecentCount == 1) {
        
    }
    if (RecentCount == 2) {
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
        [_database executeUpdate:@"update iBillList set recentBillID = 1 where billID = ?",theBill.billID];
    }
    if (RecentCount == 3) {
        [_database executeUpdate:@"update iBillList set recentBillID = NULL where recentBillID = 3"];
        [_database executeUpdate:@"update iBillList set recentBillID = 3 where recentBillID = 2"];
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
        [_database executeUpdate:@"update iBillList set recentBillID = 1 where billID = ?",theBill.billID];
    }
    if (RecentCount == 0) {
        [_database executeUpdate:@"update iBillList set recentBillID = NULL where recentBillID = 3"];
        [_database executeUpdate:@"update iBillList set recentBillID = 3 where recentBillID = 2"];
        [_database executeUpdate:@"update iBillList set recentBillID = 2 where recentBillID = 1"];
        [_database executeUpdate:@"update iBillList set recentBillID = 1 where billID = ?",theBill.billID];
    }
    //改
    if([_database executeUpdate:@"update iBillList set isPayOut = ?,billMoney=?,billTime=?,billImageData=?,billRemark=?,billTypeID=?,billTypeSubID=?,billAccountID=?,recentBillID=?,billAccountSubID=?,billlatitude=?,billlongitude=?,billNoteID=? where billID=?",theBill.isPayOut,theBill.billMoney,theBill.billTime,theBill.billImageData,theBill.billRemark,theBill.billTypeID,theBill.billTypeSubID,theBill.billAccountID,@(1),theBill.billAccountSubID,theBill.billlatitude,theBill.billlongitude,theBill.billNoteID,theBill.billID])
    {
        NSLog(@"修改账单成功");
        return YES;
    }
    return NO;
}
-(BOOL)ModifyBillNote:(iBillNote*)aNote{        //编辑账本
    [_database executeUpdate:@"update iBillNoteList set noteName=? where noteID=?",aNote.noteName,aNote.noteID];
    return YES;
}

-(NSMutableArray *)FindBill:(iBill*)theBill{    //查
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where billType = %@",kBillList,theBill.billType];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    NSMutableArray *list = [[NSMutableArray alloc]init];
    iBill *aBill;
    while ([rs next]) {
        aBill = [[iBill alloc]init];
        aBill.billID = [NSNumber numberWithInt:[rs intForColumn:@"billID"]];
        aBill.isPayOut = [NSNumber numberWithBool:[rs boolForColumn:@"isPayOut"]];
        aBill.billMoney = [NSNumber numberWithInt:[rs intForColumn:@"billMoney"]];
        aBill.billType = [rs stringForColumn:@"billType"];
        aBill.billTime = [rs dateForColumn:@"billTime"];
        aBill.billNoteName = [rs stringForColumn:@"billNoteName"];
        aBill.accountType = [rs stringForColumn:@"accountType"];
        aBill.billImageData = [rs dataForColumn:@"billImageData"];
        aBill.billRemark = [rs stringForColumn:@"billRemark"];
        [list addObject:aBill];
    }
    
    return list;
}
-(NSMutableArray *)ShowAllBill{
    NSMutableArray *list = [[NSMutableArray alloc]init];
    
    return list;
}
-(NSMutableArray *)ShowUpToDateBillWithNumber:(int)number{
    NSMutableArray *list = [[NSMutableArray alloc]init];
    for (int i = 0; i < number; i++) {
        NSString *selectDataStr = [NSString stringWithFormat:@"select * from iBillList where recentBillID = %d",i+1];
        FMResultSet *rs = [_database executeQuery:selectDataStr];
        
        iBill *aBill;
        while ([rs next]) {
            aBill = [[iBill alloc]init];
            aBill.billID = [NSNumber numberWithInt:[rs intForColumn:@"billID"]];
            aBill.isPayOut = [NSNumber numberWithBool:[rs boolForColumn:@"isPayOut"]];
            aBill.billMoney = [NSNumber numberWithDouble:[rs doubleForColumn:@"billMoney"]];
            aBill.billTypeID = [NSNumber numberWithInt:[rs intForColumn:@"billTypeID"]];
            aBill.billTypeSubID = [NSNumber numberWithInt:[rs intForColumn:@"billTypeSubID"]];
            aBill.billAccountID = [NSNumber numberWithInt:[rs intForColumn:@"billAccountID"]];
            aBill.billAccountSubID = [NSNumber numberWithInt:[rs intForColumn:@"billAccountSubID"]];
            aBill.billTime = [rs dateForColumn:@"billTime"];
            aBill.billNoteName = [rs stringForColumn:@"billNoteName"];
            aBill.accountType = [rs stringForColumn:@"accountType"];
            aBill.billImageData = [rs dataForColumn:@"billImageData"];
            aBill.billRemark = [rs stringForColumn:@"billRemark"];
            aBill.billlatitude = [NSNumber numberWithDouble:[rs doubleForColumn:@"billlatitude"]];
            aBill.billlongitude = [NSNumber numberWithDouble:[rs doubleForColumn:@"billlongitude"]];
            [list addObject:aBill];
        }
    }
    return list;
}
-(NSMutableArray *)showBillByDate:(NSDate *)startDate toDate:(NSDate *)endDate toType:(iBillType *)type{
    NSMutableArray *list = [[NSMutableArray alloc]init];
    float startNumber = startDate.timeIntervalSince1970;
    float endNumber = endDate.timeIntervalSince1970;
    NSString *selectDataStr;
    if (type == nil) {
        selectDataStr = [NSString stringWithFormat:@"select * from iBillList where billTime>='%f' and billTime <='%f'",startNumber,endNumber];
    }
    else{
        selectDataStr = [NSString stringWithFormat:@"select * from iBillList where billTime>='%f' and billTime <='%f' and billTypeID = %d",startNumber,endNumber,type.typeID.integerValue];
    }
    FMResultSet *rs = [_database executeQuery:selectDataStr];
    
    iBill *aBill;
    while ([rs next]) {
        aBill = [[iBill alloc]init];
        aBill.billID = [NSNumber numberWithInt:[rs intForColumn:@"billID"]];
        aBill.isPayOut = [NSNumber numberWithBool:[rs boolForColumn:@"isPayOut"]];
        aBill.billMoney = [NSNumber numberWithDouble:[rs doubleForColumn:@"billMoney"]];
        aBill.billTypeID = [NSNumber numberWithInt:[rs intForColumn:@"billTypeID"]];
        aBill.billTypeSubID = [NSNumber numberWithInt:[rs intForColumn:@"billTypeSubID"]];
        aBill.billAccountID = [NSNumber numberWithInt:[rs intForColumn:@"billAccountID"]];
        aBill.billAccountSubID = [NSNumber numberWithInt:[rs intForColumn:@"billAccountSubID"]];
        aBill.billTime = [rs dateForColumn:@"billTime"];
        aBill.billNoteName = [rs stringForColumn:@"billNoteName"];
        aBill.accountType = [rs stringForColumn:@"accountType"];
        aBill.billImageData = [rs dataForColumn:@"billImageData"];
        aBill.billRemark = [rs stringForColumn:@"billRemark"];
        aBill.billlatitude = [NSNumber numberWithDouble:[rs doubleForColumn:@"billlatitude"]];
        aBill.billlongitude = [NSNumber numberWithDouble:[rs doubleForColumn:@"billlongitude"]];
        [list addObject:aBill];
    }
    return list;
}
-(BOOL)addNewUser:(User *)user{
    if ([_database executeUpdate:@"insert into UserInfo (userID,userName,userImageData) values (?,?,?)",user.userID,user.userName,user.userImageData]){
        NSLog(@"添加用户成功");
        return YES;
    }
    
    return NO;
}
-(BOOL)ModifUser:(User *)user{
    if([_database executeUpdate:@"update UserInfo set userName = ?,userImageData = ? where userID = 1",user.userName,user.userImageData])
    {
        return YES;
    }
    return NO;
}
-(User *)showUser{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from UserInfo where userID = 1"];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    User *aUser;
    while ([rs next]) {
        aUser = [[User alloc]init];
        
        aUser.userID = [NSNumber numberWithInt:[rs intForColumn:@"userID"]];
        aUser.userName = [rs stringForColumn:@"userName"];
        aUser.userImageData= [rs dataForColumn:@"userImageData"];
    }
    return aUser;
}
-(BOOL)jugeThedayIsHaveBill:(NSDate *)date{
    
    return YES;
}
//TODO:账本📒
//显示当前账本
-(iBillNote *)showCurrentlyByNoteName:(NSString *)name{
    iBillNote *billNote;
    NSString *sqlStr = [NSString stringWithFormat:@"select * from iBillNoteList where noteName = ?"];
    FMResultSet *rs = [_database executeQuery:sqlStr,name];
    
    
    while ([rs next]) {
        billNote = [[iBillNote alloc]init];
        
        billNote.noteID = [NSNumber numberWithInt:[rs intForColumn:@"noteID"]];
        billNote.noteImageData = [rs dataForColumn:@"noteImageData"];
        billNote.noteName = [rs stringForColumn:@"noteName"];
        billNote.noteBillNumber = [NSNumber numberWithInt:[rs intForColumn:@"noteBillNumber"]];
        
        
    }
    self.currentNote = billNote;
    return billNote;
}
//显示账本
-(NSMutableArray *)ShowAllBillNote{
    NSMutableArray *list = [[NSMutableArray alloc]init];
    iBillNote *billNote;
    NSString *sqlStr = [NSString stringWithFormat:@"select * from iBillNoteList"];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    
    while ([rs next]) {
        billNote = [[iBillNote alloc]init];
        
        billNote.noteID = [NSNumber numberWithInt:[rs intForColumn:@"noteID"]];
        billNote.noteImageData = [rs dataForColumn:@"noteImageData"];
        billNote.noteName = [rs stringForColumn:@"noteName"];
        billNote.noteBillNumber = [NSNumber numberWithInt:[rs intForColumn:@"noteBillNumber"]];
        
        [list addObject:billNote];
    }
    return list;
}

//TODO:显示所有的BillType
-(iBillType*)showBillTypeByTypeID:(int)typeID{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where typeID = %d",kBillType,typeID];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    iBillType *type;
    while ([rs next]) {
        type = [[iBillType alloc]init];
        type.typeIsPayOut = [NSNumber numberWithBool:[rs boolForColumn:@"typeIsPayOut"]];
        type.typeID = [NSNumber numberWithInt:[rs intForColumn:@"typeID"]];
        type.typeImageData = [rs dataForColumn:@"typeImageData"];
        type.typeName = [rs stringForColumn:@"typeName"];
    }
    return type;
}


-(NSMutableArray *)showAllBillType:(BOOL)isPayOut{
    NSMutableArray *payTypeList = [[NSMutableArray alloc]init];
    NSMutableArray *outTypeList = [[NSMutableArray alloc]init];
    int count = [_database intForQuery:@"select count(typeID) from iBillType"];
    if (count == 0) {
        NSString *path = [[NSBundle mainBundle]pathForResource:@"iBillType" ofType:@"plist"];
        NSArray *list = [[NSArray alloc]initWithContentsOfFile:path];
        iBillType *type;
        for (NSDictionary *dic in list) {
            type = [[iBillType alloc]init];
            type.typeID = dic[@"id"];
            type.typeName = dic[@"name"];
            type.typeIsPayOut = dic[@"isPayOut"];
            UIImage *image = [UIImage imageNamed:dic[@"image"]];
            type.typeImageData = UIImagePNGRepresentation(image);
            NSArray *array = dic[@"list"];
            iBillTypeSub *typeSub;
            for (NSDictionary *dicSub in array) {
                typeSub = [[iBillTypeSub alloc]init];
                typeSub.typeID = type.typeID;

                typeSub.typeSubName = dicSub[@"name"];
                UIImage *imageSub = [UIImage imageNamed:dicSub[@"image"]];
                typeSub.typeSubImageData = UIImagePNGRepresentation(imageSub);
                [self addNewBillTypeSub:typeSub];
                [type.typeList addObject:typeSub];
            }
            [self addNewBillType:type];
            if (type.typeIsPayOut.boolValue == YES) {
                [outTypeList addObject:type];
            }
            else{
                
                [payTypeList addObject:type];
            }
        }
    }
    else{
        
        NSString *sqlStr = [NSString stringWithFormat:@"select * from %@",kBillType];
        FMResultSet *rs = [_database executeQuery:sqlStr];
        
        iBillType *type;
        while ([rs next]) {
            type = [[iBillType alloc]init];
            type.typeIsPayOut = [NSNumber numberWithBool:[rs boolForColumn:@"typeIsPayOut"]];
            type.typeID = [NSNumber numberWithInt:[rs intForColumn:@"typeID"]];
            type.typeImageData = [rs dataForColumn:@"typeImageData"];
            type.typeName = [rs stringForColumn:@"typeName"];
            type.typeList = [self showBillTypeSubById:type.typeID.intValue];
            type.budgetMoney = [NSNumber numberWithFloat:[rs doubleForColumn:@"budgetMoney"]];
            //BOOL is = type.typeIsPayOut.boolValue;
            if (type.typeIsPayOut.boolValue == YES) {
                [outTypeList addObject:type];
            }
            else{
                
                [payTypeList addObject:type];
            }
        }
        
    }
    if (isPayOut == YES) {
        return payTypeList;
    }
    else{
        return outTypeList;
    }
}
-(BOOL)addNewBillType:(iBillType *)theType{
    
    if ([_database executeUpdate:@"insert into iBillType (typeID,typeName,typeImageData,typeIsPayOut) values (?,?,?,?)",theType.typeID,theType.typeName,theType.typeImageData,theType.typeIsPayOut]){
        NSLog(@"添加账单类别成功");
        return YES;
    }
    
    return NO;
}
-(iBillTypeSub *)showBillSubTypeByTypeID:(int)typeSubID{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where typeSubID = %d",kBillTypeSub,typeSubID];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    iBillTypeSub *typesub;
    while ([rs next]) {
        typesub = [[iBillTypeSub alloc]init];
        
        typesub.typeSubID = [NSNumber numberWithInt:[rs intForColumn:@"typeSubID"]];
        typesub.typeID = [NSNumber numberWithInt:[rs intForColumn:@"typeID"]];
        typesub.typeSubName= [rs stringForColumn:@"typeSubName"];
        typesub.typeSubImageData = [rs dataForColumn:@"typeSubImageData"];
    }
    return typesub;
}
-(NSMutableArray *)showBillTypeSubById:(int)subId{
    NSMutableArray *list = [[NSMutableArray alloc]init];
   
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where typeID = %d",kBillTypeSub,subId];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    iBillTypeSub *typesub;
    while ([rs next]) {
        typesub = [[iBillTypeSub alloc]init];
        
        typesub.typeSubID = [NSNumber numberWithInt:[rs intForColumn:@"typeSubID"]];
        typesub.typeID = [NSNumber numberWithInt:[rs intForColumn:@"typeID"]];
        typesub.typeSubName= [rs stringForColumn:@"typeSubName"];
        typesub.typeSubImageData = [rs dataForColumn:@"typeSubImageData"];
        typesub.budgetMoney = [NSNumber numberWithFloat:[rs doubleForColumn:@"budgetMoney"]];
        
        [list addObject:typesub];
    }
    
    return list;
}
-(BOOL)addNewBillTypeSub:(iBillTypeSub *)theTypeSub{
    if ([_database executeUpdate:@"insert into iBillTypeSub (typeSubName,typeSubImageData,typeID) values (?,?,?)",theTypeSub.typeSubName,theTypeSub.typeSubImageData,theTypeSub.typeID]){
        NSLog(@"添加账单子类别成功");
        return YES;
    }
    return NO;
}
-(BOOL)removeABillType:(iBillType *)type{
    if ([_database executeUpdate:@"delete from iBillType where typeID = ?",type.typeID]){
        NSLog(@"删除账单类别成功");
        return YES;
    }
    return NO;
}
-(BOOL)removeABillSubType:(iBillTypeSub *)subtype{
    if ([_database executeUpdate:@"delete from iBillTypeSub where typeSubID = ?",subtype.typeSubID]){
        NSLog(@"删除账单子类别成功");
        return YES;
    }
    return NO;
}
-(BOOL)ModifyBillTypeBudgetMoney:(id)type withMoney:(float)money{
    if ([type isKindOfClass:[iBillType class]]) {
        iBillType *iType = (iBillType*)type;
        if([_database executeUpdate:@"update iBillType set budgetMoney = ? where typeID = ?",@(money),iType.typeID])
        {
            return YES;
        }
    }else{
        iBillTypeSub *iTypeSub = (iBillTypeSub*)type;
        if([_database executeUpdate:@"update iBillTypeSub set budgetMoney = ? where typeSubID = ?",@(money),iTypeSub.typeSubID])
        {
            return YES;
        }
    }
    return NO;
}
//TODO:显示所有的帐户
-(NSMutableArray *)showAllbillAccount{
    NSMutableArray *accountList = [[NSMutableArray alloc]init];;
    int count = [_database intForQuery:@"select count(accountID) from iBillAccount"];
    if (count == 0) {
        NSString *path = [[NSBundle mainBundle]pathForResource:@"account" ofType:@"plist"];
        NSArray *list = [[NSArray alloc]initWithContentsOfFile:path];
        iBillAccount *account;
        for (NSDictionary *dic in list) {
            account = [[iBillAccount alloc]init];
            account.accountID = dic[@"id"];
            account.accountMoney = @(0);
            account.accountName = dic[@"name"];
            NSArray *array = dic[@"list"];
            iBillAccountSub *accountSub;
            for (NSDictionary *dicSub in array) {
                accountSub = [[iBillAccountSub alloc]init];
                accountSub.accountID = account.accountID;
                accountSub.accounSubName = dicSub[@"name"];
                
                [self addNewBillAccountSub:accountSub];
                [account.accountList addObject:accountSub];
            }
            [accountList addObject:account];
            [self addNewBillAccount:account];
        }
    }
    else{
        
        NSString *sqlStr = [NSString stringWithFormat:@"select * from %@",kBillAccount];
        FMResultSet *rs = [_database executeQuery:sqlStr];
        
        iBillAccount *account;
        while ([rs next]) {
            account = [[iBillAccount alloc]init];
            
            account.accountID = [NSNumber numberWithInt:[rs intForColumn:@"accountID"]];
            account.accountMoney = [NSNumber numberWithDouble:[rs doubleForColumn:@"accountMoney"]];
            account.accountName = [rs stringForColumn:@"accountName"];
            account.accountList = [self showAllbillAccountSubById:account.accountID.intValue];
            
            [accountList addObject:account];
        }
        
    }
    return accountList;
}
-(BOOL)addNewBillAccount:(iBillAccount *)theAccount{
    if ([_database executeUpdate:@"insert into iBillAccount (accountID,accountName,accountMoney,accountImageData) values (?,?,?,?)",theAccount.accountID,theAccount.accountName,theAccount.accountMoney,theAccount.accountImageData]){
        NSLog(@"添加账户成功");
        return YES;
    }
   
    return NO;
}
-(NSMutableArray *)showAllbillAccountSubById:(int)subId{
    NSMutableArray *list = [[NSMutableArray alloc]init];
    
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where accountID = %d",kBillAccountSub,subId];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    iBillAccountSub *accountsub;
    while ([rs next]) {
        accountsub = [[iBillAccountSub alloc]init];
        
        accountsub.accountSubID = [NSNumber numberWithInt:[rs intForColumn:@"accountSubID"]];
        accountsub.accountID = [NSNumber numberWithInt:[rs intForColumn:@"accountID"]];
        accountsub.accounSubName= [rs stringForColumn:@"accountSubName"];
        
        
        [list addObject:accountsub];
    }
    
    return list;
}
-(BOOL)addNewBillAccountSub:(iBillAccountSub *)theAccountSub{
    if ([_database executeUpdate:@"insert into iBillAccountSub (accountSubName,accountSubImageData,accountID) values (?,?,?)",theAccountSub.accounSubName,theAccountSub.accounSubImageData,theAccountSub.accountID]){
        NSLog(@"添加账户子类别成功");
        return YES;
    }
    return NO;
}
-(iBillAccountSub *)showAAccountByBillAccountSubID:(int)accountSubID{
    NSString *sqlStr = [NSString stringWithFormat:@"select * from %@ where accountSubID = %d",kBillAccountSub,accountSubID];
    FMResultSet *rs = [_database executeQuery:sqlStr];
    
    iBillAccountSub *accountsub;
    while ([rs next]) {
        accountsub = [[iBillAccountSub alloc]init];
        
        accountsub.accountSubID = [NSNumber numberWithInt:[rs intForColumn:@"accountSubID"]];
        accountsub.accountID = [NSNumber numberWithInt:[rs intForColumn:@"accountID"]];
        accountsub.accounSubName= [rs stringForColumn:@"accountSubName"];
    }
    return accountsub;
}
//>>>>>>>>>>>>>>>>>>>
- (NSString *)getSettings{
    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
    NSString *str =  [defaults objectForKey:@"noteName"];
    return str;
}

- (void)setSettings{
    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
    [defaults setValue:_currentNote.noteName forKey:@"noteName"];
    
}


-(BOOL)closeDataBase{
    if([_database close])
    {
        _manager = nil;
        NSLog(@"成功关闭数据库");
        return  YES;
    }
    NSLog(@"关闭数据库失败");
    return NO;
}
@end
